package model;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.*;

import javax.naming.NamingException;
import javax.sql.RowSet;
import javax.sql.rowset.CachedRowSet;

import org.apache.struts.upload.FormFile;

import com.sun.rowset.CachedRowSetImpl;

import db.DbConnectionPool;
import db.DbException;
import forms.ReviewerFormInsert;
import forms.ReviewerFormDelete;

public class StudentModel {

	private static String selectCoun = "SELECT country FROM countries ORDER BY id"; 
	private static String selectUniv = "SELECT location university FROM universities ORDER BY id";
	private static String selectRev = "SELECT rev_id,username,password FROM Reviewers ORDER BY rev_id";
	private static String insertRev = "INSERT INTO Reviewers(rev_id,username,password, appGraded, appNotGra) VALUES (?,?, md5(?),?,?)";
	private static String revToUsr = "INSERT INTO users(user_id,user_name,password,revID) VALUES(?,?,md5(?),?)";
	private static String revToUsrRole = "INSERT INTO user_roles(user_ref, user_name, role,revID)" +
    "VALUES(?,?,?,?)"; 
	private static String deleteRev = "DELETE FROM Reviewers WHERE rev_id = ?";
	private static String delRevUsr = "DELETE FROM users WHERE revID = ?";
	private static String delRevUsrRole = "DELETE FROM user_roles where revID = ?";
	
	private static int numRev = 0;
	
	
	public static CachedRowSet getCountries() throws DbException {

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = DriverManager.getConnection(url,"postgres","postgres");

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectCoun);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allCountries = pStmt.executeQuery();

			CachedRowSet crsCountries = new CachedRowSetImpl();
			crsCountries.populate(allCountries);

			allCountries.close();
			pStmt.close();
			conn.close();

			return crsCountries;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} 
	}
	
	public static CachedRowSet getUnivercities() throws DbException {

		try {
			Connection conn = DriverManager.getConnection(
			"jdbc:postgresql://localhost:5432/Applications", "postgres",
			"postgres");

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectUniv);

			// Use the created statement to SELECT
			// the student attributes FROM the Student table.
			ResultSet allUnivercities = pStmt.executeQuery();

			CachedRowSet crsUnivercities = new CachedRowSetImpl();
			crsUnivercities.populate(allUnivercities);

			allUnivercities.close();
			pStmt.close();
			conn.close();

			return crsUnivercities;
		} catch (SQLException ex) {
			throw new DbException(ex);
		} 
	}
	
	public static CachedRowSet getAllReviewers() throws DbException {

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = DriverManager.getConnection(url,"postgres","postgres");

			// Create the statement
			PreparedStatement pStmt = conn.prepareStatement(selectRev);

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			ResultSet allReviewers = pStmt.executeQuery();

			CachedRowSet crsReviewers = new CachedRowSetImpl();
			crsReviewers.populate(allReviewers);

			allReviewers.close();
			pStmt.close();
			conn.close();

			return crsReviewers;
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void insertReviewer(ReviewerFormInsert reviewer)
		throws DbException 
	{
		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			PreparedStatement pStmt = null;
			ResultSet rs = null;
			int userid = 0, revid = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			rs = stmt.executeQuery("SELECT rev_id FROM Reviewers");
			while(rs.next())
			{
		        revid = rs.getInt("rev_id");
			}
			revid++;
			
			// Create the prepared statement and use it to
			// INSERT reviewer values INTO the Reviewers table.
			pStmt = conn.prepareStatement(insertRev);
			pStmt.setInt(1, revid);
			pStmt.setString(2, reviewer.getUsername());
			pStmt.setString(3, reviewer.getPassword());
			pStmt.setInt(4, 0);
			pStmt.setInt(5, 0);
			pStmt.executeUpdate();
			
			rs = stmt.executeQuery("SELECT user_id FROM users");
			while(rs.next())
			{
		        userid = rs.getInt("user_id");
			}
			userid++;
			
			pStmt = conn.prepareStatement(revToUsr);
			pStmt.setInt(1, userid);
			pStmt.setString(2,reviewer.getUsername());
			pStmt.setString(3,reviewer.getPassword());
			pStmt.setInt(4,revid);
			pStmt.executeUpdate();
			
			pStmt = conn.prepareStatement(revToUsrRole);
			pStmt.setInt(1, userid);
			pStmt.setString( 2, reviewer.getUsername() );
			pStmt.setString( 3, "reviewer");
			pStmt.setInt(4,revid);
			pStmt.executeUpdate();
		
			// Commit transaction
			conn.commit();
		
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	public static void deleteReviewer(ReviewerFormDelete reviewer)
	throws DbException {
		
		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			PreparedStatement pStmt = null;
			
			pStmt = conn.prepareStatement(delRevUsrRole);
			pStmt.setInt(1, reviewer.getId());
			pStmt.executeUpdate();
			
			pStmt = conn.prepareStatement(delRevUsr);
			pStmt.setInt(1, reviewer.getId());
			pStmt.executeUpdate();
	
			// Create the prepared statement and use it to
			// DELETE reviewers FROM the Reviewers table.
			pStmt = conn.prepareStatement(deleteRev);
			pStmt.setInt(1, reviewer.getId());
			pStmt.executeUpdate();
				
			// Commit transaction
			conn.commit();
		
			pStmt.close();
			conn.close();
		} catch (SQLException ex) {
			throw new DbException(ex);
		}
	}
	
	public static int numReviewers() 
	{
		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			
			numRev = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			rs = stmt.executeQuery("SELECT rev_id FROM Reviewers");
			while(rs.next())
			{
		        numRev = rs.getInt("rev_id");
			}
			
		
			// Commit transaction
			conn.commit();

			conn.close();
		} catch (SQLException ex) {
			System.out.println(ex);
		}
		
		return numRev;
	}
	
	public static CachedRowSet getNameInfo(String username) throws DbException
	{

		CachedRowSet crsName = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT user_name,APP FROM users WHERE user_name = '"+ username + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("APP");
			}
			
			pStmt = conn.prepareStatement("SELECT First, Middle, Last FROM NAME WHERE NameID ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsName = new CachedRowSetImpl();
			crsName.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
			return crsName;
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsName;
	}
	
	public static CachedRowSet getAddrInfo(String username) throws DbException
	{

		CachedRowSet crsAddr = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT AddID FROM MainTable WHERE NameID = "
							+"(SELECT distinct APP FROM users WHERE user_name = '"+ username + "')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("AddID");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM ADDRESS WHERE AddID ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsAddr = new CachedRowSetImpl();
			crsAddr.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsAddr;

	}
	
	public static CachedRowSet getCounInfo(String username) throws DbException
	{

		CachedRowSet crsCoun = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT CounID FROM MainTable WHERE NameID = "
							+"(SELECT distinct APP FROM users WHERE user_name = '"+ username + "')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("CounID");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM COUNTRY WHERE CounID ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsCoun = new CachedRowSetImpl();
			crsCoun.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsCoun;

	}
	
	public static CachedRowSet getCounName(int id) throws DbException
	{

		CachedRowSet crsCounName = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT Country FROM Countries WHERE ID = '"+ id + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsCounName = new CachedRowSetImpl();
			crsCounName.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsCounName;

	}

	public static CachedRowSet getMajor(int id) throws DbException
	{

		CachedRowSet crsMajorName = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT Discipline FROM Disciplines WHERE ID = '"+ id + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsMajorName = new CachedRowSetImpl();
			crsMajorName.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsMajorName;

	}

	
	
	
	public static CachedRowSet getSchool(int id) throws DbException
	{

		CachedRowSet crsSchoolName = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT * FROM Universities WHERE ID = '"+ id + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsSchoolName = new CachedRowSetImpl();
			crsSchoolName.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsSchoolName;

	}
	
	
	public static CachedRowSet getDegInfo(String username) throws DbException
	{

		CachedRowSet crsDeg = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT DegID FROM MainTable WHERE NameID = "
							+"(SELECT distinct APP FROM users WHERE user_name = '"+ username + "')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("DegID");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM DEGREE WHERE ID ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsDeg = new CachedRowSetImpl();
			crsDeg.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsDeg;

	}
	
	public static CachedRowSet getSpecInfo(String username) throws DbException
	{

		CachedRowSet crsSpec = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT SpecID FROM MainTable WHERE NameID = "
							+"(SELECT distinct APP FROM users WHERE user_name = '"+ username + "')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("SpecID");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM Specializations WHERE ID ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsSpec = new CachedRowSetImpl();
			crsSpec.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsSpec;

	}
	
	public static CachedRowSet getRevInfo(String username) throws DbException
	{

		CachedRowSet crsRev = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT AppID FROM MainTable WHERE NameID = "
							+"(SELECT distinct APP FROM users WHERE user_name = '"+ username + "')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.
			rs = pStmt.executeQuery();
			
			while( rs.next() )
			{
				app = rs.getInt("AppID");
			}
			
			pStmt = conn.prepareStatement("SELECT * FROM Application WHERE app_id ='"+app+"'");
			
			rs = pStmt.executeQuery();
			 
			crsRev = new CachedRowSetImpl();
			crsRev.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsRev;

	}
	
	public static CachedRowSet getRevName(int id) throws DbException
	{

		CachedRowSet crsRevName = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT username FROM Reviewers WHERE rev_id = '"+ id + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsRevName = new CachedRowSetImpl();
			crsRevName.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsRevName;

	}

	public static CachedRowSet getDegreeID(String username) throws DbException
	{

		CachedRowSet crsDegIDs = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT DegID FROM MainTable WHERE NameID = "
											+ "(SELECT APP FROM users WHERE user_name = '"+
											username +"')");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsDegIDs = new CachedRowSetImpl();
			crsDegIDs.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsDegIDs;

	}
	
	
	public static CachedRowSet getDegrees(int id) throws DbException
	{

		CachedRowSet crsDeg = null;

		try {
			String url = "jdbc:postgresql://localhost:5432/Applications";
			Class.forName("org.postgresql.Driver");

			Connection conn = DriverManager.getConnection(url,"postgres","postgres");
			Statement stmt = null;
			ResultSet rs = null;
			PreparedStatement pStmt = null;
			int app = 0;
		
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
			
			// Create the statement
			pStmt = conn.prepareStatement("SELECT * FROM DEGREE WHERE DegID = '"+ id + "'");

			// Use the created statement to SELECT
			// the reviewer attributes FROM the Reviewers table.			
			rs = pStmt.executeQuery();
			 
			crsDeg = new CachedRowSetImpl();
			crsDeg.populate(rs);			
		
			// Commit transaction
			conn.commit();

			rs.close();
			conn.close();	
			
		} catch (SQLException ex) {
			throw new DbException(ex);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		return crsDeg;

	}
}
